import pymysql;
import time;
import random;
class DataBase:

    def __init__(self, host, port, user, paswd, db):
        self._conn = pymysql.connect(host=host, port=port, user=user, passwd=paswd, db=db);
        self._conn.autocommit(True);

    def createNewGame(self, userOne, userTwo):
        if(userOne.userId == userTwo.userId):
            return -1;
        else:
            cursor = self._conn.cursor();
            cursor.execute('INSERT INTO games (player_one, player_two) values ('+userOne.userId +', '+ userTwo.userId +');');
            cursor.execute('SELECT game_id FROM games ORDER BY game_id DESC;');
            result = str(cursor.fetchone()[0]);
            cursor.close();
            return result;

    def createNewPlayerIfNotExist(self, userId, name):
        cursor = self._conn.cursor();
        cursor.execute('SELECT playerId FROM players WHERE playerId = '+str(userId)+';');
        if(not bool(cursor.fetchone())):
            cursor.execute("INSERT INTO players (playerId, playerName) values ("+str(userId)+", '"+str(name)+"');");
            cursor.execute('SELECT playerId FROM players WHERE playerId = '+userId+';');
        result = str(cursor.fetchone()[0]);
        cursor.close();
        return result;

    def setWinner(self, gameId, winner, conclusion):
        cursor = self._conn.cursor();
        cursor.execute("UPDATE games SET winner='" + winner.userId + "', game_conclusion='" + conclusion + "' WHERE game_id = '"+str(gameId)+"';");
        cursor.close();

    def getStatistics(self, page, userId):
        return self.getPlayerScores(page, "players.playerId");

    def getPlayerScores(self, page, userId):
        cursor = self._conn.cursor();
        offset = page * 10;
        query = 'SELECT * FROM (';
        query = query + 'SELECT players.playerName as name, players.playerId as id,';
        query = query + ' SUM(conclusions.scores) as scores, @row:= @row + 1 \'rank\'';
        query = query + ' FROM players, games, conclusions, (SELECT @row:=0)';
        query = query + ' WHERE games.winner = players.playerId';
        query = query + ' AND conclusions.conclusion = games.game_conclusion';
        query = query + ' GROUP BY id ORDER BY scores DESC';
        #query = query + ' LIMIT ' + str(offset) + ','+ str(offset + 10);
        query = query + ') as results WHERE id = ' + str(userId);
        cursor.execute(query);
        data = cursor.fetchall();
        result = list();
        rowIndex = 0;
        for row in data:
            result.append(row);
        return result;